<?php

function aprobarOrden($idOrden, $usuario){
    $array_prop = parse_ini_file('DataAcces.properties');
    $enlace = mysqli_connect($array_prop['servidor'], $array_prop['usuario'], $array_prop['contra'], $array_prop['base']);
    if (mysqli_connect_errno()) {
        printf("Conexión fallida: %s\n", mysqli_connect_error());
        exit();
    }
    
    $consulta = "SELECT sxr.stepCode"
            . " FROM u225435723_orden.SEC_USER usr "
            . " INNER JOIN u225435723_orden.STEP_X_ROLE sxr ON (sxr.roleCode = usr.roleCode)"
            . " WHERE usr.userName = '".$usuario."' AND sxr.stepCode NOT IN ('CNX','DEL','DND','CHD','CRE')";
    
    if ($resultado = mysqli_query($enlace, $consulta)) {
        $row = mysqli_fetch_assoc($resultado);
        
        $update = "UPDATE u225435723_orden.ORDEN SET step='".$row['stepCode']."' WHERE idOrden=".$idOrden;
        
        $result = mysqli_query($enlace, $update);

        mysqli_commit($enlace);
        mysqli_free_result($resultado);
        
        echo "aproved";
    }
    mysqli_close($enlace);
    
    sendEstatusNotificationMail($idOrden);
    
    
}

function nuevoEstadoOrden($idOrden, $usuario, $estado) {
    $array_prop = parse_ini_file('DataAcces.properties');

    $enlace = mysqli_connect($array_prop['servidor'], $array_prop['usuario'], $array_prop['contra'], $array_prop['base']);

    /* verificar la conexión */
    if (mysqli_connect_errno()) {
        printf("Conexión fallida: %s\n", mysqli_connect_error());
        exit();
    }
    
    $update = "UPDATE u225435723_orden.ORDEN SET step='".$estado."' WHERE idOrden=".$idOrden;
        
    $result = mysqli_query($enlace, $update);

    mysqli_commit($enlace);
        
    echo "changed";
    mysqli_close($enlace);
    
    sendEstatusNotificationToOwnerMail($idOrden);
}

function sendEstatusNotificationToOwnerMail($idOrden){
    $array_prop = parse_ini_file('DataAcces.properties');

    $enlace = mysqli_connect($array_prop['servidor'], $array_prop['usuario'], $array_prop['contra'], $array_prop['base']);

    /* verificar la conexión */
    if (mysqli_connect_errno()) {
        printf("Conexión fallida: %s\n", mysqli_connect_error());
        exit();
    }
    
    $consulta = "SELECT o.creationUser,CONCAT_WS(' ',usr.firstName,usr.LastName) AS userName ,usr.userMail, s.stepName"
                . " FROM u225435723_orden.ORDEN o"
                . " INNER JOIN u225435723_orden.SEC_USER usr ON (o.creationUser = usr.userName)"
                . " INNER JOIN u225435723_orden.STEP s ON (o.step = s.stepCode)"
                . " WHERE idOrden = " . $idOrden;
    
    $results = "";
    if ($resultado = mysqli_query($enlace, $consulta)) {
        /* obtener array asociativo */
        while ($row = mysqli_fetch_assoc($resultado)) {
            $to = $row['userMail'];
            $subject = "Orden # " . $idOrden . " has changed status";
            $txt = "Hello " .$row['userName'].".\n\r The orden # " . $idOrden . " changed it's status to ".$row['stepName'];
            $headers = "From: njserranor@gmail.com";
            mail($to,$subject,$txt,$headers);
        }
        /* liberar el conjunto de resultados */
        mysqli_free_result($resultado);
    }

    /* cerrar la conexión */
    mysqli_close($enlace);
}
function sendEstatusNotificationMail($idOrden){
    $array_prop = parse_ini_file('DataAcces.properties');

    $enlace = mysqli_connect($array_prop['servidor'], $array_prop['usuario'], $array_prop['contra'], $array_prop['base']);

    /* verificar la conexión */
    if (mysqli_connect_errno()) {
        printf("Conexión fallida: %s\n", mysqli_connect_error());
        exit();
    }

    $consulta = "SELECT DISTINCT u.userMail,u.userName,s.stepName " 
                . " FROM u225435723_orden.ORDEN o"
                . " INNER JOIN u225435723_orden.STEP s ON (o.step = s.stepCode) "
                . " INNER JOIN u225435723_orden.STEP_X_ROLE sxr ON (s.stepCode = sxr.stepCode) "
                . " INNER JOIN u225435723_orden.NEXT_STEP ns ON (sxr.stepCode = ns.step) "
                . " INNER JOIN u225435723_orden.STEP_X_ROLE r ON (ns.next = r.stepCode) "
                . " INNER JOIN u225435723_orden.SEC_USER u ON (r.roleCode = u.roleCode) "
                . " WHERE o.idOrden = " . $idOrden . " AND ns.next NOT IN ('CHD','DEL','CNX','DND')";
    
    $results = "";
    if ($resultado = mysqli_query($enlace, $consulta)) {
        /* obtener array asociativo */
        while ($row = mysqli_fetch_assoc($resultado)) {
            $to = $row['userMail'];
            $subject = "Orden # " . $idOrden . " has changed status";
            $txt = "Hello " .$row['userName'].".\n\r The orden # " . $idOrden . " changed it's status to ".$row['stepName'];
            $headers = "From: njserranor@gmail.com";
            mail($to,$subject,$txt,$headers);
        }
        /* liberar el conjunto de resultados */
        mysqli_free_result($resultado);
    }

    /* cerrar la conexión */
    mysqli_close($enlace);
}